package com.pur.zsms.quotation.dao.impl;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.company.po.CompanyPo;
import com.pur.zsms.quotation.dao.IQuotationDao;
import com.pur.zsms.quotation.po.QuotationCompanyPeriodPo;
import com.pur.zsms.quotation.po.QuotationCompanyPeriodSubPo;
import com.pur.zsms.quotation.po.QuotationPo;
import com.pur.zsms.quotation.po.QuotationSubPo;
import com.pur.zsms.quotation.vo.QuotationCompanyPeriodSubVo;
import com.pur.zsms.quotation.vo.QuotationCompanyPeriodVo;
import com.pur.zsms.quotation.vo.QuotationVo;

@Repository("quotationDao")
public class QuotationImpl extends GenericDao implements IQuotationDao {
	public boolean checkName(String name, long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from QuotationPo o where o.supId='" + supId + "' AND o.quotationName ='" + name + "'");
		List<QuotationPo> list = template.find(ql.toString(), null);

		if (list != null && list.size() > 0) {
			return false;
		}
		return true;
	}

	public void addQuotation(QuotationPo c) throws GenericException {
		this.persist(c);
	}

	public void saveQuotationSub(QuotationSubPo c) throws GenericException {
		this.persist(c);
	}

	public void deleteQuotation(long quotationId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete QuotationPo o where o.quotationId=" + quotationId);
		template.executeUpdate(ql.toString());

		StringBuffer ql2 = new StringBuffer();
		ql2.append(" delete QuotationSubPo o where o.quotationId=" + quotationId);
		template.executeUpdate(ql2.toString());
	}

	public PaginationSupport listQuotation(PageController pageController, QuotationVo quotationVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT q.order_id,q.mater_type,q.raise_amount FROM q_quotation_sub q WHERE  q.quotation_id = '" + quotationVo.getQuotationId()
				+ "'");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<QuotationPo> findQuotationPo(long supId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from QuotationPo o where o.supId='" + supId + "' ");
		List<QuotationPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public PaginationSupport companyPeriodList(PageController pageController, QuotationCompanyPeriodVo tQuotationCompanyPeriodVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT q.`quotation_id`,q.`quotation_name`,q.`quotation_date`,q.`quotation_flag`,c.company_id,c.`company_name` FROM q_quotation_company_period q ,t_company c WHERE  q.`company_id` = c.`company_id` AND q.`sup_id` ='"
				+ tQuotationCompanyPeriodVo.getSupId() + "'");

		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport detailRaisePricePeriod(PageController pageController, QuotationCompanyPeriodVo tQuotationCompanyPeriodVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT qs.sub_id,qs.`quotation_id`,qs.`mater_id`,qs.`mater_value`,m.`mater_name`,m.`unit`,m.`specifications`,m.`brands`,m.mater_type FROM q_quotation_company_period q,q_quotation_company_period_sun qs,t_material m WHERE m.`mater_id`=qs.`mater_id` AND qs.`quotation_id`=q.`quotation_id` AND  qs.`quotation_id` ='"
				+ tQuotationCompanyPeriodVo.getQuotationId() + "'");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public void addQuotationCompanyPeriodPo(QuotationCompanyPeriodPo c) throws GenericException {
		this.persist(c);
	}

	public void updateQuotationCompanyPeriodPo(QuotationCompanyPeriodPo c) throws GenericException {
		this.merge(c);
	}

	public void addQuotationCompanyPeriodSubPo(QuotationCompanyPeriodSubPo c) throws GenericException {
		this.persist(c);
	}

	public void updateQuotationCompanyPeriodSubPo(QuotationCompanyPeriodSubPo c) throws GenericException {
		this.merge(c);
	}

	public List<CompanyPo> getCompanyPo() throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from CompanyPo o ");
		List<CompanyPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public List<CompanyPo> getCompanyPoBySupId(long supId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT o FROM CompanyPo o WHERE EXISTS (SELECT 1 FROM CompanySupPo a WHERE a.supId= '" + supId
				+ "' AND a.companyId =o.companyId )");
		List<CompanyPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public List<QuotationCompanyPeriodPo> getQuotationCompanyPeriodPoBySupIdAndCompanyId(int supId, int companyId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from QuotationCompanyPeriodPo o WHERE o.supId ='" + supId + "' AND o.companyId = '" + companyId + "' ");
		List<QuotationCompanyPeriodPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public List<QuotationCompanyPeriodPo> getQuotationCompanyPeriodPo(QuotationCompanyPeriodVo vo) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from QuotationCompanyPeriodPo o WHERE 1=1 ");
		if (vo.getSupId() != 0) {
			sql.append(" and o.supId ='" + vo.getSupId() + "' ");
		}

		if (vo.getCompanyId() != 0) {
			sql.append("  and o.companyId = '" + vo.getCompanyId() + "'  ");
		}

		if (vo.getQuotationFlag() != 0) {
			sql.append(" and o.quotationFlag = '" + vo.getQuotationFlag() + "'  ");
		}

		List<QuotationCompanyPeriodPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public List<QuotationCompanyPeriodPo> getQuotationCompanyPeriodPoByQuotationId(long quotationId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from QuotationCompanyPeriodPo o WHERE o.quotationId ='" + quotationId + "' ");
		List<QuotationCompanyPeriodPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}

	public void deleteCompanyPeriod(long quotationId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete QuotationCompanyPeriodPo o where o.quotationId=" + quotationId);
		template.executeUpdate(ql.toString());

		StringBuffer ql2 = new StringBuffer();
		ql2.append(" delete QuotationCompanyPeriodSubPo o where o.quotationId=" + quotationId);
		template.executeUpdate(ql2.toString());
	}

	public List<Object[]> getCompanyPeriodForExcel(long quotationId) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT qs.mater_id, m.mater_name, (SELECT c.describ FROM t_code c WHERE c.code_type_id = 'mater_type' AND c.code_oder = m.mater_type) AS materType , m.brands, m.specifications, m.unit, qs.mater_value FROM q_quotation_company_period q, q_quotation_company_period_sun qs, t_material m WHERE q.quotation_id = qs.quotation_id AND m.mater_id = qs.mater_id AND q.quotation_id='"
				+ quotationId + "' order by materType,m.mater_name");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object[]> getCompanyPeriodValue(int materId, int supId, long companyId) throws GenericException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT qs.mater_value FROM q_quotation_company_period q, q_quotation_company_period_sun qs WHERE q.quotation_id = qs.quotation_id AND q.sup_id = '"
				+ supId + "' AND q.company_id = '" + companyId + "' AND q.quotation_flag = 1 AND qs.mater_id ='" + materId + "'");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<QuotationCompanyPeriodSubPo> getQuotationCompanyPeriodSubPo(QuotationCompanyPeriodSubVo vo) throws GenericException {
		StringBuffer sql = new StringBuffer();
		sql.append("select o from QuotationCompanyPeriodSubPo o WHERE 1=1 ");
		if (vo.getQuotationId() != 0) {
			sql.append(" and o.quotationId ='" + vo.getQuotationId() + "' ");
		}

		if (vo.getMaterId() != 0) {
			sql.append(" and o.materId ='" + vo.getMaterId() + "' ");
		}

		List<QuotationCompanyPeriodSubPo> list = this.getTemplate().find(sql.toString(), null);
		return list;
	}
}
